SELECT
' ' AS `ลำดับที่`,
person.pid AS HN,
CONCAT(ctitle.titlename,person.fname,' ',person.lname) AS `ชื่อ - สกุล`,
house.hno AS 'บ้านเลขที่',
village.villno AS `หมู่ที่`,
person.rightcode as 'สิทธิ',
visit.visitdate as 'วันรับบริการ',
cdisease.diseasenamethai AS `โรค`
FROM
person
INNER JOIN ctitle ON ctitle.titlecode = person.prename
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
INNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid
INNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode AND visit.visitno = visitdiag.visitno
INNER JOIN cdisease ON visitdiag.diagcode = cdisease.diseasecode
WHERE
visit.visitdate BETWEEN '2022-05-01' AND '2022-05-31' and person.rightcode in ('l1','l2')
GROUP BY visit.visitno
ORDER BY visitdate DESC;